#include "sqlitetools.h"



#include <QDate>
#include<QString>
#include<QVector>

//带参数的构造函数，进行私有属性的初始化
SQLiteTools::SQLiteTools(QString dbName, QString connName)
{
  dataBaseName = dbName;
  connectionName = connName;
}

SQLiteTools::~SQLiteTools()
{

}

void SQLiteTools::initDataBase()
{
  //qDebug()<<QSqlDatabase::drivers();//打印驱动列表
  //检测已连接的方式 - 自定义连接名
  if(QSqlDatabase::contains(connectionName))
    db = QSqlDatabase::database(connectionName);
  else
    db = QSqlDatabase::addDatabase("QSQLITE",connectionName);

  //设置数据库路径，不存在则创建
  db.setDatabaseName("./autoTarget.db");
  //db.setUserName("gongjianbo");  //SQLite不需要用户名和密码
  //db.setPassword("qq654344883");



  //打开数据库，验证是否成功
  if(db.open()){
      qDebug()<<"open success";
      //关闭数据库
      //
    }else{
      db.close();
    }
}

bool SQLiteTools::openDateBase()
{
  if(!db.open())
    {
      qDebug() << "Error: Failed to open database." << db.lastError();
      return false;
    }
  return true;
}

void SQLiteTools::closeDataBase()
{
  db.close();
}

void SQLiteTools::createTable()
{
  //如果不存在则创建my_table表,id自增,name唯一
    const QString sql = R"(
                        CREATE TABLE IF NOT EXISTS target_info_table(
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        addr INTEGER NOT NULL,
                        x REAL NOT NULL,
                        y REAL NOT NULL,
                        system_id INTEGER NOT NULL,
                        cylinder_number INTEGER NOT NULL,
                        direction INTEGER NOT NULL,
                        group_number INTEGER NOT NULL,
                        user_id INTEGER NOT NULL,
                        is_delete INTEGER NOT NULL DEFAULT 0,
                        create_at TEXT DEFAULT CURRENT_DATE
                        );)";
  //QSqlQuery构造前，需要db已打开并连接
  //未指定db或者db无效时使用默认连接进行操作
  QSqlQuery query(db);
  if(query.exec(sql)){
      qDebug()<<"init table success";
    }else{
      //打印sql语句错误信息
      qDebug()<<"init table error"<<query.lastError();
    }
}

//插入数据
void SQLiteTools::insertRecord(const Target_Info_Table& targetInfoTable)
{
  //方式一，绑定值，待定变量默认用问号占位,注意字符串也没有引号
  //query.prepare(R"(INSERT INTO my_table(name,age) VALUES(?,?);)");
  //query.addBindValue(name);
  //query.addBindValue(age);
  //query.exec();

  QSqlQuery query(db);
  QString sql = QString(R"(INSERT INTO target_info_table(addr,x,y,system_id,
                        cylinder_number,direction,group_number,user_id,is_delete,create_at)
                        VALUES(%1,%2,%3,%4,%5,%6,%7,%8,%9,'%10');)")
      .arg(targetInfoTable.addr).arg(targetInfoTable.x).arg(targetInfoTable.y)
      .arg(targetInfoTable.SystemID).arg(targetInfoTable.cylinder_number)
      .arg(targetInfoTable.direction).arg(targetInfoTable.group_number)
      .arg(targetInfoTable.UserId).arg(0).arg(targetInfoTable.CreateAt);

  query.prepare(sql);
  if(!query.exec())
    {
      qDebug() << query.lastError();
    }
  else
    {
      qDebug() << "inserted OK!";
    }
  query.finish();
}

//删除数据
void SQLiteTools::deleteRecord(int addr)
{
  //方式一，直接执行SQL语句
  //query.exec(QString(R"(DELETE FROM my_table WHERE name='%1';)")
  //           .arg(name));
  //方式二，绑定值，待定变量默认用问号占位
  /*query.prepare(R"(DELETE FROM my_table WHERE name=?;)");
    query.addBindValue(name);
    query.exec();*/

  QSqlQuery query(db);
  QString sql;
  if(addr < 0)
    sql = QString(R"(UPDATE target_info_table SET is_delete=%1; )").arg(1);
  else
    sql = QString(R"(UPDATE target_info_table SET is_delete=%1 WHERE addr = %2)").arg(1).arg(addr);
  query.prepare(sql);
  if(!query.exec())
    {
      qDebug() << query.lastError();
    }
  else
    {
      qDebug() << "deleted OK!";
    }
  query.finish();
}

//更新数据
void SQLiteTools::updateRecord(int id, qreal px, qreal py)
{
  //方式一，绑定值，待定变量默认问号，可自定义
  /*query.prepare(R"(UPDATE my_table SET age=:age WHERE name=:name;)");
    query.bindValue(":name",name);//通过自定义的别名来替代
    query.bindValue(":age",age);
    query.exec();*/

  QSqlQuery query(db);
  QString sql = QString(R"(UPDATE target_info_table SET x=%2, y=%3 WHERE addr=%1;)").arg(id).arg(px).arg(py);
  query.prepare(sql);
  if(!query.exec())
    {
      qDebug() << query.lastError();
    }
  else
    {
      qDebug() << "updated OK!";
    }
  query.clear();
  query.finish();

}

//查询数据
QVector<Target_Info_Table> SQLiteTools::searchCurrentDate(int groupNumber,int addr)
{
  //获取查询结果，如果结果是多行数据，可用while(query.next()){}遍历每一行
  QDate m_date = QDate::currentDate();
  QString currentDate = m_date.toString("yyyy-MM-dd");

  QSqlQuery query(db);
  //  QString sql = QString(R"(SELECT * FROM target_info_table)");
  QString sql = QString(R"(SELECT * FROM target_info_table WHERE is_delete = %1 AND group_number = %2 AND addr = %3 AND create_at = '%4';)").arg(0).arg(groupNumber).arg(addr).arg(currentDate);
  query.prepare(sql);

  QVector<Target_Info_Table> targetInfo;
  if(!query.exec())
    {
      qDebug()<<query.lastError();
    }
  else
    {
      while(query.next())
        {
          Target_Info_Table target_Info_Table;

          target_Info_Table.addr = query.value(1).toInt();
          target_Info_Table.x = query.value(2).toReal();
          target_Info_Table.y = query.value(3).toReal();
          target_Info_Table.cylinder_number = query.value(5).toInt();
          target_Info_Table.direction = query.value(6).toInt();
          targetInfo.push_back(target_Info_Table);
        }
    }
  query.clear();
  query.finish();


  return targetInfo;

}

QVector<Target_Info_Table> SQLiteTools::searchName(int groupNumber)
{
  //获取查询结果，如果结果是多行数据，可用while(query.next()){}遍历每一行
  QDate m_date = QDate::currentDate();
  QString currentDate = m_date.toString("yyyy-MM-dd");

  QSqlQuery query(db);
  //  QString sql = QString(R"(SELECT * FROM target_info_table)");
  QString sql = QString(R"(SELECT * FROM target_user_table WHERE is_delete = %1 AND ethnic_groups = %2 AND create_at = '%3';)").arg(0).arg(groupNumber).arg(currentDate);
  query.prepare(sql);

  QVector<Target_Info_Table> targetInfo;
  if(!query.exec())
    {
      qDebug()<<query.lastError();
    }
  else
    {
      while(query.next())
        {
          Target_Info_Table target_Info_Table;

          target_Info_Table.addr = query.value(0).toInt();
          target_Info_Table.user_name = query.value(1).toString();

          targetInfo.push_back(target_Info_Table);
        }
    }
  query.clear();
  query.finish();


  return targetInfo;
}



QVector<Target_Info_Table> SQLiteTools::searchGradeTable(int groupNumber, int addr, QString currentDate)
{

  QSqlQuery query(db);
  QString sql;
  //QString sql = QString(R"(SELECT * FROM target_info_table)");
  //  QString sql = QString(R"(SELECT * FROM target_info_table WHERE group_number = %1 AND addr = %2 AND create_at = '%3';)").arg(groupNumber).arg(addr).arg(date);

//  QString sql = QString(R"(SELECT target_info_table.addr,target_info_table.group_number,target_user_table.user_name FROM target_info_table INNER JOIN target_user_table ON target_info_table.user_id =  target_user_table.id WHERE target_info_table.group_number = %1 AND target_info_table.addr = %2 AND target_info_table.create_at = '%3';)").arg(groupNumber).arg(addr).arg(date);
// SELECT target_info_table.addr,target_info_table.cylinder_number,target_info_table.group_number,target_user_table.user_name FROM target_info_table INNER JOIN target_user_table ON target_info_table.user_id =  target_user_table.id WHERE target_info_table.group_number = 1 AND target_info_table.addr = 1 AND target_info_table.create_at = '2023-04-09';

  if(groupNumber == 0 )
  {
        //      sql = QString(R"(SELECT target_info_table.addr,target_info_table.cylinder_number,target_info_table.group_number,target_user_table.user_name FROM target_info_table INNER JOIN target_user_table ON target_info_table.user_id =  target_user_table.id WHERE target_info_table.addr = %1 AND target_info_table.create_at = '%2';)").arg(addr).arg(currentDate);
        sql = QString(R"(SELECT target_info_table.addr,target_info_table.cylinder_number,target_info_table.group_number FROM target_info_table WHERE target_info_table.addr = %1 AND target_info_table.create_at = '%2';)").arg(addr).arg(currentDate);
  }else if(addr == 0)
  {
        //      sql = QString(R"(SELECT target_info_table.addr,target_info_table.cylinder_number,target_info_table.group_number,target_user_table.user_name FROM target_info_table INNER JOIN target_user_table ON target_info_table.user_id =  target_user_table.id WHERE target_info_table.group_number = %1 AND target_info_table.create_at = '%2';)").arg(groupNumber).arg(currentDate);
        sql = QString(R"(SELECT target_info_table.addr,target_info_table.cylinder_number,target_info_table.group_number FROM target_info_table WHERE target_info_table.group_number = %1 AND target_info_table.create_at = '%2';)").arg(groupNumber).arg(currentDate);

  }else
  {
        //      sql = QString(R"(SELECT target_info_table.addr,target_info_table.cylinder_number,target_info_table.group_number,target_user_table.user_name FROM target_info_table INNER JOIN target_user_table ON target_info_table.user_id =  target_user_table.id WHERE target_info_table.group_number = %1 AND target_info_table.addr = %2 AND target_info_table.create_at = '%3';)").arg(groupNumber).arg(addr).arg(currentDate);
        sql = QString(R"(SELECT target_info_table.addr,target_info_table.cylinder_number,target_info_table.group_number FROM target_info_table WHERE target_info_table.group_number = %1 AND target_info_table.addr = %2 AND target_info_table.create_at = '%3';)").arg(groupNumber).arg(addr).arg(currentDate);
  }

  query.prepare(sql);

  QVector<Target_Info_Table> targetInfo;
  if(!query.exec())
    {
      qDebug()<<query.lastError();
    }
  else
    {
      while(query.next())
        {
          Target_Info_Table target_Info_Table;

          target_Info_Table.addr = query.value(0).toInt();

          target_Info_Table.cylinder_number = query.value(1).toInt();

          target_Info_Table.group_number = query.value(2).toInt();

          if(query.value(3).toString().size() != 0)

            target_Info_Table.user_name = query.value(3).toString();
          else{
              target_Info_Table.user_name = "";
            }

          targetInfo.push_back(target_Info_Table);
        }

    }
  query.clear();
  query.finish();


  return targetInfo;
}

//获取记录行数
int SQLiteTools::getRowCounts(int groupNumber,int addr)
{
  int counts = -1;
  QDate m_date = QDate::currentDate();
  QString currentDate = m_date.toString("yyyy-MM-dd");
  QSqlQuery query(db);

  QString sql = QString(R"(SELECT count(*) FROM target_info_table WHERE is_delete = %1 AND group_number = %2 AND addr = %3 AND create_at = '%4';)").arg(0).arg(groupNumber).arg(addr).arg(currentDate);
  query.prepare(sql);
  if(!query.exec())
    {
      qDebug() << query.lastError();
    }
  else
    {
      if (query.next()) {
          counts = query.value(0).toInt();
      }
    }
  query.clear();
  query.finish();

  return counts;
}
